package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.Product;

import ModelLayer.Supplier;

public class DBProduct implements IFDBProduct {
	private Connection con;

	public DBProduct() {
		con = DBConnection.getInstance().getDBcon();
	}

	@Override
	public Product getProduct(int productId, boolean retriveAssociation) {
		String wClause = "  productId = '" + productId + "'";
		return singleWhere(wClause, retriveAssociation);
	}

	@Override
	public int insertProduct(Product product) throws Exception {
		int nextId = GetMaxId.getMaxId("Select max(productId) From product") + 1;
		int rc = -1;
		int belowMin;
		if (product.isBelowMin()) {
			belowMin = 1;
		} else {
			belowMin = 0;
		}
		String query = "INSERT INTO product(productId, name, payPrice, salePrice, rentPrice, countryOfOrigin, minStock, currentStock, isBelowMin, supplier_Id, clothSize, color, equipmentType, description, fabric, calibre) VALUES('"
				+ nextId
				+ "','"
				+ product.getName()
				+ "','"
				+ product.getPayPrice()
				+ "','"
				+ product.getSalePrice()
				+ "','"
				+ product.getRentPrice()
				+ "','"
				+ product.getCountryOfOrigin()
				+ "','"
				+ product.getMinStock()
				+ "','"
				+ product.getCurrentStock()
				+ "','"
				+ belowMin
				+ "','"
				+ product.getSupplier().getId()
				+ "','"
				+ product.getClothSize()
				+ "','"
				+ product.getColor()
				+ "','"
				+ product.getEquipmentType()
				+ "','"
				+ product.getDescription()
				+ "','"
				+ product.getFabric()
				+ "','"
				+ product.getCalibre()
				+ "')";
		System.out.println("insert : " + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException ex) {
			System.out.println("product is not inserted");
			throw new Exception("product is not inserted correct");
		}
		return rc;
	}

	@Override
	public ArrayList<Product> getAllProduct(boolean retriveAssociation) {
		return miscWhere("", retriveAssociation);
	}

	@Override
	public int updateProduct(Product product) {
		Product productObj = product;
		int rc = -1;
		int belowMin;
		if (product.isBelowMin()) {
			belowMin = 1;
		} else {
			belowMin = 0;
		}
		String query = "UPDATE product SET" + "name = '" + productObj.getName()
				+ "'," + "payPrice = '" + productObj.getSalePrice() + "',"
				+ "salePrice = '" + productObj.getSalePrice() + "',"
				+ "rentPrice = '" + productObj.getRentPrice() + "',"
				+ "countryOfOrigin = '" + productObj.getCountryOfOrigin()
				+ "'," + "minStock = '" + productObj.getMinStock() + "',"
				+ "currentStock = '" + productObj.getCurrentStock() + "',"
				+ "isBelowMin = '" + belowMin + "'," + "supplier_Id = '"
				+ productObj.getSupplier().getId() + "'," + "clothSize = '"
				+ productObj.getClothSize() + "'," + "color = '"
				+ productObj.getColor() + "'," + "equipmentType = '"
				+ productObj.getEquipmentType() + "'," + "description = '"
				+ productObj.getDescription() + "'," + "fabric = '"
				+ productObj.getFabric() + "'," + "calibre = '"
				+ productObj.getCalibre() + "'," + " WHERE productId = '"
				+ productObj.getProductId() + "'";
		System.out.println("Update query:" + query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (Exception e) {
			System.out.println("Update exception in product db: " + e);
		}
		return rc;
	}

	private Product singleWhere(String wClause, boolean retriveAssociation) {
		ResultSet results;
		Product product = new Product();
		String query = buildQuery(wClause);
		System.out.println(query);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			if (results.next()) {
				product = buildProduct(results);
				stmt.close();
				if (retriveAssociation) {
					IFDBSupplier dbSupplier = new DBSupplier();
					Supplier supplier = dbSupplier.getSupplier(product
							.getSupplier().getId(), false);
					product.setSupplier(supplier);
					System.out.println("supplier is selected");

				} else {
					product = null;
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return product;
	}

	private ArrayList<Product> miscWhere(String wClause,
			boolean retriveAssociation) {
		ResultSet results;
		ArrayList<Product> list = new ArrayList<Product>();
		String query = buildQuery(wClause);
		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while (results.next()) {
				Product product = new Product();
				product = buildProduct(results);
				list.add(product);
			}
			stmt.close();
			if (retriveAssociation) {
				for (Product product : list) {
					IFDBSupplier dbSupplier = new DBSupplier();
					Supplier supplier = dbSupplier.getSupplier(product
							.getSupplier().getId(), false);
					product.setSupplier(supplier);
					System.out.println("supplier is selected");
				}
			}
		} catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private Product buildProduct(ResultSet results) {
		Product product = new Product();
		try {
			product.setProductId(results.getInt("productId"));
			product.setName(results.getString("name"));
			product.setPayPrice(results.getDouble("payPrice"));
			product.setSalePrice(results.getDouble("salePrice"));
			product.setCountryOfOrigin(results.getString("countryOfOrigin"));
			product.setMinStock(results.getInt("minStock"));
			product.setCurrentStock(results.getInt("currentStock"));
			product.setSupplier(new Supplier(results.getInt("supplier_Id")));
			product.setClothSize(results.getInt("clothSize"));
			product.setColor(results.getString("color"));
			product.setEquipmentType(results.getString("equipmentType"));
			product.setDescription(results.getString("description"));
			product.setFabric(results.getString("fabric"));
			product.setCalibre(results.getString("calibre"));
			if (results.getInt("isBelowMin") == 1) {
				product.setBelowMin(true);
			} else {
				product.setBelowMin(false);
			}
		} catch (Exception e) {
			System.out.println("error in building the product object");
		}
		return product;
	}

	private String buildQuery(String wClause) {
		String query = "SELECT productId, name, payPrice, salePrice, rentPrice, countryOfOrigin, minStock, currentStock, isBelowMin, supplier_Id, clothSize, color, equipmentType, description, fabric, calibre FROM product";
		if (wClause.length() > 0) {
			query = query + " WHERE" + wClause;
		}
		return query;
	}
}
